package il.ac.tau.dbcourse.db;

import il.ac.tau.dbcourse.db.annotations.SQLTable;

import java.lang.annotation.Annotation;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class PersistenceManager {
	@SuppressWarnings("unchecked")
	protected static String getTableName(Class c) {
		Annotation anno = c.getAnnotation(SQLTable.class);
		if (anno != null) {

			SQLTable sqlTable = (SQLTable) anno;
			if (sqlTable.table().compareTo("") != 0) {
				return sqlTable.table();
			}
		}
		return "";
	}
//ctxcat
	public static boolean isExists(Connection conn,
			Class<? extends AbsPersistence> c, long id) {
		PreparedStatement stmt=null;
		ResultSet rs=null;
		try {
			stmt = conn
					.prepareStatement("select ID \"COUNT\" FROM "
							+ getTableName(c) + " where ID=?");
			stmt.setLong(1, id);
			rs = stmt.executeQuery();
			boolean result = rs.next();
			rs.close();
			stmt.close();
			return result;
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			try {
				if (stmt != null)
					stmt.close();
				if (rs != null)
					rs.close();
			} catch (Exception e) {
			}
		}
		return false;
	}

	public static void delete(Connection conn,
			Class<? extends AbsPersistence> c, long id) {
		PreparedStatement stmt=null;
		try {
			String tableName = getTableName(c);
			stmt = conn.prepareStatement("delete from "
					+ tableName + " where id=?");
			stmt.setLong(1, id);
			stmt.executeUpdate();
			stmt.close();

		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			try {
				if (stmt != null)
					stmt.close();
			} catch (Exception e) {
			}
		}
	}

	protected static Long getId(Connection conn,
			Class<? extends AbsPersistence> c, String rowId) {
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			String tableName = getTableName(c);
			stmt = conn.prepareStatement("select * from " + tableName
					+ " where rowId=?");
			stmt.setString(1, rowId);

			rs = stmt.executeQuery();
			if (!rs.next())
				throw new SQLException();
			Long id = rs.getLong("ID");

			return id;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				rs.close();
			} catch (SQLException e) {
			}
			try {
				stmt.close();
			} catch (SQLException e) {
			}
		}
		return null;
	}

	public static void clearDB(Connection conn) {
		try {
			truncate(conn);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public static Long getRandom(Connection conn,
			Class<? extends AbsPersistence> c) {
		try {
			PreparedStatement stmt = conn.prepareStatement("select ID from "
					+ "(select ID from " + getTableName(c) + " "
					+ "order by dbms_random.value) WHERE rownum = 1");
			ResultSet rs = stmt.executeQuery();
			if (!rs.next())
				throw new SQLException();
			Long id = rs.getLong("ID");
			rs.close();
			stmt.close();
			return id;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

	public static Long save(Connection conn, AbsPersistence persistence) {
		return persistence.saveImpl(conn);
	}

	private static void resetSeq(Connection conn, String seqStr)
			throws SQLException {
		Statement stmt = conn.createStatement();
		stmt.execute("DROP SEQUENCE\"" + seqStr + "\"");
		stmt.execute("CREATE SEQUENCE\"" + seqStr + "\"");
		stmt.close();
	}

	public static void truncate(Connection conn) throws SQLException {
		truncate(conn, true);
	}

	public static void truncate(Connection conn, boolean fkStatus)
			throws SQLException {
		toggleFK(conn, false);
		Statement stmt = conn.createStatement();
		stmt.execute("TRUNCATE TABLE USER_TRACK");
		stmt.execute("TRUNCATE TABLE INCOMPLETE_ALBUM");
		stmt.execute("TRUNCATE TABLE MISSING_TRACK");
		stmt.execute("TRUNCATE TABLE TRACK");
		stmt.execute("TRUNCATE TABLE ALBUM");
		stmt.execute("TRUNCATE TABLE GENRE");
		stmt.execute("TRUNCATE TABLE ARTIST");

		resetSeq(conn, "ALBUM_SEQ");
		resetSeq(conn, "ARTIST_SEQ");
		resetSeq(conn, "GENRE_SEQ");
		resetSeq(conn, "TRACK_SEQ");

		toggleFK(conn, fkStatus);
	}

	public static void disableCTXindices(Connection conn) throws SQLException {
		Statement stmt = conn.createStatement();
		try {
			stmt.execute("DROP INDEX ARTIST_CTX1");
		} catch (SQLException e) {
		}
		try {
			stmt.execute("DROP INDEX ALBUM_CTX1");
		} catch (SQLException e) {
		}
		try {
			stmt.execute("DROP INDEX TRACK_CTX1");
		} catch (SQLException e) {
		}
	}

	public static void rebuildCTXindices(Connection conn) throws SQLException {
		Statement stmt = conn.createStatement();
		stmt
				.execute(" CREATE INDEX ARTIST_CTX1 ON ARTIST(NAME) INDEXTYPE IS ctxsys.context");
		stmt
				.execute(" CREATE INDEX TRACK_CTX1 ON TRACK(TITLE) INDEXTYPE IS ctxsys.context");
		stmt
				.execute(" CREATE INDEX ALBUM_CTX1 ON ALBUM(TITLE) INDEXTYPE IS ctxsys.context");
	}

	public static void toggleFK(Connection conn, boolean flag)
			throws SQLException {
		// Disable foreign keys
		String state = (flag) ? "ENABLE" : "DISABLE";
		Statement stmt = conn.createStatement();
		stmt.execute("ALTER TABLE USER_TRACK " + state
				+ " CONSTRAINT USER_TRACK_FK");
		stmt.execute("ALTER TABLE ALBUM " + state + " CONSTRAINT ALBUM_FK");
		stmt.execute("ALTER TABLE ALBUM " + state + " CONSTRAINT ALBUM_FK2");
		stmt.execute("ALTER TABLE TRACK " + state + " CONSTRAINT TRACK_FK");
		stmt.execute("ALTER TABLE MISSING_TRACK " + state
				+ " CONSTRAINT MISSING_TRACK_FK");
		stmt.execute("ALTER TABLE INCOMPLETE_ALBUM " + state
				+ " CONSTRAINT INCOMPLETE_ALBUM_FK");

		stmt.close();
	}

	public static void truncateUserTrack(Connection conn) throws SQLException {
		Statement stmt = conn.createStatement();
		stmt.execute("TRUNCATE TABLE USER_TRACK");
	}

	public static void toggleAutoIDs(Connection conn, boolean flag)
			throws SQLException {
		String state = (flag) ? "ENABLE" : "DISABLE";
		Statement stmt = conn.createStatement();
		stmt.execute("ALTER TRIGGER BI_ALBUM " + state);
		stmt.execute("ALTER TRIGGER BI_ARTIST " + state);
		stmt.execute("ALTER TRIGGER BI_GENRE " + state);
		stmt.execute("ALTER TRIGGER BI_TRACK " + state);

		stmt.close();
	}
}
